package com.qf.dao.impl;

import com.qf.dao.LivedPetDao;
import com.qf.entity.LivedPet;
import com.qf.utils.DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class LivedPetDaoImpl implements LivedPetDao {
    QueryRunner qr= DBUtils.DB.getQr();

    @Override
    public Integer selectLivedPetCount(String name, String startTime, String endTime, String type) throws SQLException {
        String sql = "select count(1) from live_pet where 1=1";
        //实例化高效字符串 实现动态sql
        StringBuffer sb = new StringBuffer(sql);
        //定义集合储存参数
        ArrayList<Object> cslist = new ArrayList<Object>();
        if (type != null && !"".equals(name)) {
            sb.append(" and type=?");
            cslist.add(type);
        }
        if (name != null && !"".equals(name)) {
            sb.append(" and name like ? ");
            cslist.add("%" + name + "%");
        }
        if (startTime != null && !"".equals(startTime)) {
            sb.append(" and arrivalday>?");
            cslist.add(startTime);
        }
        if (endTime != null && !"".equals(endTime)) {
            sb.append(" and arrivalday<?");
            cslist.add(endTime);
        }
        Long count = null;
        //查询 先判断是否集合有值
        if (cslist.size() > 0) {
            count = (Long) qr.query(sb.toString(), new ScalarHandler(), cslist.toArray());
        } else {
            count = (Long) qr.query(sb.toString(), new ScalarHandler());
        }
        // Long count= (Long) qr.query(sb.toString(), new ScalarHandler(), cslist.toArray());
        return Integer.valueOf(count + "");

    }

    @Override
    public List<LivedPet> selectLivedPetList(String name, String startTime, String endTime, String type, Integer currentPageNo,Integer pageSize) throws SQLException {
        String sql = "select * from live_pet where 1=1";
        //实例化高效字符串 实现动态sql
        StringBuffer sb = new StringBuffer(sql);
        //定义集合储存参数
        ArrayList<Object> cslist = new ArrayList<Object>();
        if (type != null && !"".equals(name)) {
            sb.append(" and type=?");
            cslist.add(type);
        }
        if (name != null && !"".equals(name)) {
            sb.append(" and name like ? ");
            cslist.add("%" + name + "%");
        }
        if (startTime != null && !"".equals(startTime)) {
            sb.append(" and arrivalday>?");
            cslist.add(startTime);
        }
        if (endTime != null && !"".equals(endTime)) {
            sb.append(" and arrivalday<?");
            cslist.add(endTime);
        }
        //拼接分页
        sb.append(" limit ?,?");
        cslist.add((currentPageNo-1)*pageSize);
        cslist.add(pageSize);
        List<LivedPet> LivedPetList = qr.query(sb.toString(), new BeanListHandler<LivedPet>(LivedPet.class), cslist.toArray());

        return LivedPetList;
    }

    @Override
    public int add(LivedPet livedPet) throws SQLException {
        //活体表sql语句
        String sql = "INSERT INTO live_pet(name,type,sex,height,weight,state,supplyPrice,retailPrice,message,birthday,arrivalday,vaccineday,inwormday,outwormday) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
        Object[] obj ={livedPet.getName(),livedPet.getType(),livedPet.getSex(),livedPet.getHeight(),livedPet.getWeight(),livedPet.getState(),livedPet.getSupplyPrice(),livedPet.getRetailPrice(),livedPet.getMessage(),
                livedPet.getBirthday(),livedPet.getArrivalday(),livedPet.getVaccineday(),livedPet.getInwormday(),livedPet.getOutwormday()};
        int num = qr.update(sql, obj);
        return num;
    }

    @Override
    public int update(LivedPet livedPet) throws SQLException {
        String sql = "UPDATE live_pet SET name=?,type=?,sex=?,height=?,weight=?,state=?,supplyPrice=?,retailPrice=?,message=?,birthday=?,arrivalday=?,vaccineday=?,inwormday=?,outwormday=? where lid=?;";
        Object[] obj ={livedPet.getName(),livedPet.getType(),livedPet.getSex(),livedPet.getHeight(),livedPet.getWeight(),livedPet.getState(),livedPet.getSupplyPrice(),livedPet.getRetailPrice(),livedPet.getMessage(),
                livedPet.getBirthday(),livedPet.getArrivalday(),livedPet.getVaccineday(),livedPet.getInwormday(),livedPet.getOutwormday(),livedPet.getLid()};
        int num = qr.update(sql, obj);
        if (num>0) {
            return num;
        }
        return -1;
    }

    @Override
    public int delete(String lid) throws SQLException {
        String sql = "DELETE FROM live_pet WHERE lid in ("+lid+")";
        int num=qr.update(sql);
        return num;
    }

    @Override
    public List<LivedPet> selectLivedPetByTime(String time) throws SQLException {
        String sql = "SELECT * FROM live_pet where vaccineday=? or inwormday=? or outwormday=?;";
        Object[] obj={time,time,time};
        List<LivedPet> livedPets = qr.query(sql, new BeanListHandler<LivedPet>(LivedPet.class), obj);
        return livedPets;
    }


}
